1 Imports System.Data.SqlClient
2
3 Public Class frmServices
4
5 Dim st2 As String
6 Sub Reset()
7 txtChargesQuote.Text = ""
8 txtCID.Text = ""
9 txtCustomerID.Text = ""
10 txtCustomerName.Text = ""
11 txtItemsDescription.Text = ""
12 txtProblemDescription.Text = ""
13 txtRemarks.Text = ""
14 txtUpfront.Text = ""
15 cmbServiceType.Text = ""
16 cmbStatus.SelectedIndex = 1
17 txtContactNo.Text = ""
18 dtpServiceCreationDate.Text = Today
19 dtpEstimatedRepairDate.Text = Today
20 btnPrint.Enabled = False
21 btnDelete.Enabled = False
22 btnUpdate.Enabled = False
23 btnSave.Enabled = True
24 auto()
25 End Sub
26 Private Function GenerateID() As String
27 con = New SqlConnection(cs)
28 Dim value As String = "0000"
29 Try
30 ' Fetch the latest ID from the database
31 con.Open()
32 cmd = New SqlCommand("SELECT TOP 1 S_ID FROM Service ORDER BY S_ID DESC", con)
33 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
34 If rdr.HasRows Then
35 rdr.Read()
36 value = rdr.Item("S_ID")
37 End If
38 rdr.Close()
39 ' Increase the ID by 1
40 value += 1
41 ' Because incrementing a string with an integer removes 0's
42 ' we need to replace them. If necessary.
43 If value <= 9 Then 'Value is between 0 and 10
44 value = "000" & value
45 ElseIf value <= 99 Then 'Value is between 9 and 100
46 value = "00" & value
47 ElseIf value <= 999 Then 'Value is between 999 and 1000
48 value = "0" & value
49 End If
50 Catch ex As Exception
51 ' If an error occurs, check the connection state and close it if necessary.
52 If con.State = ConnectionState.Open Then
53 con.Close()
54 End If
55 value = "0000"
56 End Try
57 Return value
58 End Function
59 Sub auto()
60 Try
61 txtID.Text = GenerateID()
62 txtServiceCode.Text = "SC-" + GenerateID()
63 Catch ex As Exception
64 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
65 End Try
66 End Sub
67 Private Sub btnSelect_Click(sender As System.Object, e As System.EventArgs) Handles btnSelect.Click
68 frmCustomerRecord2.lblSet.Text = "Services"
69 frmCustomerRecord2.lblUser.Text = lblUser.Text
70 frmCustomerRecord2.Reset()
71 frmCustomerRecord2.ShowDialog()
72 End Sub
73
74 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
75 Me.Close()
76 End Sub
77
78 Sub Print()
79 Try
80
81 Cursor = Cursors.WaitCursor
82 Timer1.Enabled = True
83 Dim rpt As New rptServiceReceipt 'The report you created.
84 Dim myConnection As SqlConnection
85 Dim MyCommand, MyCommand1 As New SqlCommand()
86 Dim myDA, myDA1 As New SqlDataAdapter()
87 Dim myDS As New DataSet 'The DataSet you created.
88 myConnection = New SqlConnection(cs)
89 MyCommand.Connection = myConnection
90 MyCommand1.Connection = myConnection
91 MyCommand.CommandText = "SELECT Service.S_ID, Service.ServiceCode, Service.ServiceType, Service.ServiceCreationDate, Service.ItemDescription, Service.ProblemDescription, Service.ChargesQuote,Service.AdvanceDeposit, Service.EstimatedRepairDate, Service.Remarks, Service.Status, Customer.ID, Customer.Name, Customer.Gender, Customer.Address, Customer.City,Customer.State, Customer.ZipCode, Customer.ContactNo, Customer.EmailID, Customer.Remarks AS Expr2, Customer.Photo FROM Service INNER JOIN Customer ON Service.CustomerID = Customer.ID where Service.ServiceCode=@d1"
92 MyCommand.Parameters.AddWithValue("@d1", txtServiceCode.Text)
93 MyCommand1.CommandText = "SELECT * from Company"
94 MyCommand.CommandType = CommandType.Text
95 MyCommand1.CommandType = CommandType.Text
96 myDA.SelectCommand = MyCommand
97 myDA1.SelectCommand = MyCommand1
98 myDA.Fill(myDS, "Service")
99 myDA.Fill(myDS, "Customer")
100 myDA1.Fill(myDS, "Company")
101 rpt.SetDataSource(myDS)
102 rpt.SetParameterValue("p1", txtCustomerID.Text)
103 rpt.SetParameterValue("p2", Today)
104 frmReport.CrystalReportViewer1.ReportSource = rpt
105 frmReport.ShowDialog()
106 Catch ex As Exception
107 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
108 End Try
109
110 End Sub
111
112
113 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
114 Try
115 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
116 DeleteRecord()
117 End If
118 Catch ex As Exception
119 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
120 End Try
121 End Sub
122 Private Sub DeleteRecord()
123
124 Try
125 Dim RowsAffected As Integer = 0
126 con = New SqlConnection(cs)
127 con.Open()
128 Dim cl As String = "SELECT S_ID FROM Service INNER JOIN InvoiceInfo1 ON Service.S_ID = InvoiceInfo1.ServiceID where S_ID=@d1"
129 cmd = New SqlCommand(cl)
130 cmd.Connection = con
131 cmd.Parameters.AddWithValue("@d1", txtID.Text)
132 rdr = cmd.ExecuteReader()
133 If rdr.Read Then
134 MessageBox.Show("Unable to delete..Already in use in Billing", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
135 If Not rdr Is Nothing Then
136 rdr.Close()
137 End If
138 Exit Sub
139 End If
140 con.Close()
141 con = New SqlConnection(cs)
142 con.Open()
143 Dim cq As String = "delete from Service where S_ID=@d1"
144 cmd = New SqlCommand(cq)
145 cmd.Parameters.AddWithValue("@d1", txtID.Text)
146 cmd.Connection = con
147 RowsAffected = cmd.ExecuteNonQuery()
148 If RowsAffected > 0 Then
149 Dim st As String = "deleted the record having service code '" & txtServiceCode.Text & "'"
150 LogFunc(lblUser.Text, st)
151 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
152 Reset()
153 fillServiceType()
154 Else
155 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
156 Reset()
157 End If
158 If con.State = ConnectionState.Open Then
159 con.Close()
160
161 End If
162 Catch ex As Exception
163 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
164 End Try
165 End Sub
166
167 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
168 If Len(Trim(txtItemsDescription.Text)) = 0 Then
169 MessageBox.Show("Please enter items description", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
170 txtItemsDescription.Focus()
171 Exit Sub
172 End If
173 If Len(Trim(txtChargesQuote.Text)) = 0 Then
174 MessageBox.Show("Please enter charges quote", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
175 txtChargesQuote.Focus()
176 Exit Sub
177 End If
178 If Len(Trim(txtUpfront.Text)) = 0 Then
179 MessageBox.Show("Please enter upfront", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
180 txtUpfront.Focus()
181 Exit Sub
182 End If
183 If Len(Trim(txtCustomerName.Text)) = 0 Then
184 MessageBox.Show("Please retrieve customer details", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
185 Exit Sub
186 End If
187 Try
188 con = New SqlConnection(cs)
189 con.Open()
190 Dim ctn As String = "select * from Company"
191 cmd = New SqlCommand(ctn)
192 cmd.Connection = con
193 rdr = cmd.ExecuteReader()
194
195 If Not rdr.Read() Then
196 MessageBox.Show("Add company profile first in master entry", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
197 If (rdr IsNot Nothing) Then
198 rdr.Close()
199 End If
200 Return
201 End If
202 con = New SqlConnection(cs)
203 con.Open()
204 Dim cb As String = "insert into Service(S_ID, ServiceCode, CustomerID, ServiceType, ServiceCreationDate, ItemDescription, ProblemDescription, ChargesQuote, AdvanceDeposit, EstimatedRepairDate, Remarks, Status) Values (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12)"
205 cmd = New SqlCommand(cb)
206 cmd.Parameters.AddWithValue("@d1", txtID.Text)
207 cmd.Parameters.AddWithValue("@d2", txtServiceCode.Text)
208 cmd.Parameters.AddWithValue("@d3", txtCID.Text)
209 cmd.Parameters.AddWithValue("@d4", cmbServiceType.Text)
210 cmd.Parameters.AddWithValue("@d5", dtpServiceCreationDate.Value.Date)
211 cmd.Parameters.AddWithValue("@d6", txtItemsDescription.Text)
212 cmd.Parameters.AddWithValue("@d7", txtProblemDescription.Text)
213 cmd.Parameters.AddWithValue("@d8", txtChargesQuote.Text)
214 cmd.Parameters.AddWithValue("@d9", txtUpfront.Text)
215 cmd.Parameters.AddWithValue("@d10", dtpEstimatedRepairDate.Value.Date)
216 cmd.Parameters.AddWithValue("@d11", txtRemarks.Text)
217 cmd.Parameters.AddWithValue("@d12", cmbStatus.Text)
218 cmd.Connection = con
219 cmd.ExecuteReader()
220 con.Close()
221 Dim st As String = "added the new service having service code '" & txtServiceCode.Text & "'"
222 LogFunc(lblUser.Text, st)
223 If CheckForInternetConnection() = True Then
224 con = New SqlConnection(cs)
225 con.Open()
226 Dim ctn1 As String = "select RTRIM(APIURL) from SMSSetting where IsDefault='Yes' and IsEnabled='Yes'"
227 cmd = New SqlCommand(ctn1)
228 cmd.Connection = con
229 rdr = cmd.ExecuteReader()
230 If rdr.Read() Then
231 st2 = rdr.GetValue(0)
232 Dim st3 As String = "Hello, " & txtCustomerName.Text & " service has been created successfully having service code " & txtServiceCode.Text & ""
233 SMSFunc(txtContactNo.Text, st3, st2)
234 If (rdr IsNot Nothing) Then
235 rdr.Close()
236 End If
237 End If
238 End If
239 con.Close()
240 btnSave.Enabled = False
241 fillServiceType()
242 MessageBox.Show("Successfully created", "Service", MessageBoxButtons.OK, MessageBoxIcon.Information)
243 Print()
244 Catch ex As Exception
245 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
246 End Try
247 End Sub
248
249 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
250 If Len(Trim(txtItemsDescription.Text)) = 0 Then
251 MessageBox.Show("Please enter items description", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
252 txtItemsDescription.Focus()
253 Exit Sub
254 End If
255 If Len(Trim(txtChargesQuote.Text)) = 0 Then
256 MessageBox.Show("Please enter charges quote", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
257 txtChargesQuote.Focus()
258 Exit Sub
259 End If
260 If Len(Trim(txtUpfront.Text)) = 0 Then
261 MessageBox.Show("Please enter upfront", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
262 txtUpfront.Focus()
263 Exit Sub
264 End If
265 If Len(Trim(txtCustomerName.Text)) = 0 Then
266 MessageBox.Show("Please retrieve customer details", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
267 Exit Sub
268 End If
269 Try
270 con = New SqlConnection(cs)
271 con.Open()
272 Dim cb As String = "Update Service set ServiceCode=@d2, CustomerID=@d3, ServiceType=@d4, ServiceCreationDate=@d5, ItemDescription=@d6, ProblemDescription=@d7, ChargesQuote=@d8, AdvanceDeposit=@d9, EstimatedRepairDate=@d10, Remarks=@d11, Status=@d12 where S_ID=@d1"
273 cmd = New SqlCommand(cb)
274 cmd.Parameters.AddWithValue("@d1", txtID.Text)
275 cmd.Parameters.AddWithValue("@d2", txtServiceCode.Text)
276 cmd.Parameters.AddWithValue("@d3", txtCID.Text)
277 cmd.Parameters.AddWithValue("@d4", cmbServiceType.Text)
278 cmd.Parameters.AddWithValue("@d5", dtpServiceCreationDate.Value.Date)
279 cmd.Parameters.AddWithValue("@d6", txtItemsDescription.Text)
280 cmd.Parameters.AddWithValue("@d7", txtProblemDescription.Text)
281 cmd.Parameters.AddWithValue("@d8", txtChargesQuote.Text)
282 cmd.Parameters.AddWithValue("@d9", txtUpfront.Text)
283 cmd.Parameters.AddWithValue("@d10", dtpEstimatedRepairDate.Value.Date)
284 cmd.Parameters.AddWithValue("@d11", txtRemarks.Text)
285 cmd.Parameters.AddWithValue("@d12", cmbStatus.Text)
286 cmd.Connection = con
287 cmd.ExecuteReader()
288 con.Close()
289 Dim st As String = "updated the service having service code '" & txtServiceCode.Text & "'"
290 LogFunc(lblUser.Text, st)
291 btnUpdate.Enabled = False
292 fillServiceType()
293 MessageBox.Show("Successfully updated", "Service", MessageBoxButtons.OK, MessageBoxIcon.Information)
294 Catch ex As Exception
295 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
296 End Try
297 End Sub
298
299 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
300 frmServicesRecord.lblSet.Text = "Services"
301 frmServicesRecord.Reset()
302 frmServicesRecord.ShowDialog()
303 End Sub
304
305 Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
306 Reset()
307 End Sub
308
309
310 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
311 Cursor = Cursors.Default
312 Timer1.Enabled = False
313 End Sub
314
315 Private Sub btnPrint_Click(sender As System.Object, e As System.EventArgs) Handles btnPrint.Click
316 Print()
317 End Sub
318
319 Private Sub txtChargesQuote_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtUpfront.KeyPress
320 Dim keyChar = e.KeyChar
321
322 If Char.IsControl(keyChar) Then
323 'Allow all control characters.
324 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
325 Dim text = Me.txtUpfront.Text
326 Dim selectionStart = Me.txtUpfront.SelectionStart
327 Dim selectionLength = Me.txtUpfront.SelectionLength
328
329 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
330
331 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
332 'Reject an integer that is longer than 16 digits.
333 e.Handled = True
334 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
335 'Reject a real number with two many decimal places.
336 e.Handled = False
337 End If
338 Else
339 'Reject all other characters.
340 e.Handled = True
341 End If
342 End Sub
343 Sub fillServiceType()
344 Try
345 con = New SqlConnection(cs)
346 con.Open()
347 adp = New SqlDataAdapter()
348 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ServiceType) FROM Service", con)
349 ds = New DataSet("ds")
350 adp.Fill(ds)
351 dtable = ds.Tables(0)
352 cmbServiceType.Items.Clear()
353 For Each drow As DataRow In dtable.Rows
354 cmbServiceType.Items.Add(drow(0).ToString())
355 Next
356 Catch ex As Exception
357 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
358 End Try
359 End Sub
360 Private Sub txtChargesQuote_KeyPress_1(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtChargesQuote.KeyPress
361 Dim keyChar = e.KeyChar
362
363 If Char.IsControl(keyChar) Then
364 'Allow all control characters.
365 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
366 Dim text = Me.txtChargesQuote.Text
367 Dim selectionStart = Me.txtChargesQuote.SelectionStart
368 Dim selectionLength = Me.txtChargesQuote.SelectionLength
369
370 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
371
372 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
373 'Reject an integer that is longer than 16 digits.
374 e.Handled = True
375 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
376 'Reject a real number with two many decimal places.
377 e.Handled = False
378 End If
379 Else
380 'Reject all other characters.
381 e.Handled = True
382 End If
383 End Sub
384
385 Private Sub frmServices_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
386 fillServiceType()
387 End Sub
388
389 Private Sub cmbServiceType_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbServiceType.Format
390 If (e.DesiredType Is GetType(String)) Then
391 e.Value = e.Value.ToString.Trim
392 End If
393 End Sub
394 End Class
